//
//  Sqlite.swift
//  Share
//
//  Created by admin on 2020/10/13.
//  Copyright © 2020 MBA. All rights reserved.
//

import Foundation
import SQLite


let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
let db = try? Connection("\(path)/share_db.sqlite")

extension Connection {
    
    public var userVersion: Int32 {
        get { return Int32(try! scalar("PRAGMA user_version") as! Int64)}
        set { try! run("PRAGMA user_version = \(newValue)") }
    }
  
}

struct TShare: Codable , Identifiable {
    
    var id = 0
    var name:String=""
    var code:String=""
    
    func encode(to encoder: Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(name, forKey: .name)
        try container.encode(code, forKey: .code)
        
    }
    
}

class SQLiteManage {
    
    
    func install()  {
        
        initShareTable()
        initStoreTable()
     
    }
    
    func isExitTable(tname:String)->Bool{
        do {
            
            let sqliteMaster = Table("sqlite_master")
            let tableCount = try db?.scalar(sqliteMaster.filter(Expression<String>("type") == "table" && Expression<String?>("name") == tname).count)
            
            return tableCount == 1
        } catch {
            return false
        }
    }
    
    func initShareTable(){
        /**
         id
         name 名称
         code 代码
         price 价格
         ratioIn 买入价格比例
         ratioOut 卖出价格比例
         */
        
        do {
            let table = Table("Share")
            if false == isExitTable(tname: "Share"){
                try db?.run(table.create { t in
                    t.column(Expression<Int64>("id"), primaryKey: true)
                    t.column(Expression<String?>("name"))
                    t.column(Expression<String?>("code"), unique: true)
                })
                UserDefaults.standard.setValue("1", forKey: "db_share")
            }
            var shareVersion:Int = UserDefaults.standard.integer(forKey: "db_share")
            if shareVersion == 1 {
                try db?.run(table.addColumn(Expression<String?>("price")))
                UserDefaults.standard.setValue("2", forKey: "db_share")
            }
            
            shareVersion = UserDefaults.standard.integer(forKey: "db_share")
            if shareVersion == 2 {
                try db?.run(table.addColumn(Expression<String?>("ratioIn")))
                try db?.run(table.addColumn(Expression<String?>("ratioOut")))
                UserDefaults.standard.setValue("3", forKey: "db_share")
            }
            shareVersion = UserDefaults.standard.integer(forKey: "db_share")
            if shareVersion == 3 {
                try db?.run(table.addColumn(Expression<String?>("yestodayprice")))
                UserDefaults.standard.setValue("4", forKey: "db_share")
            }
       
        }catch {
            debugPrint("\(self) \(#function) SQLiteManage install error \(error)")
        }
    }
    
    func initStoreTable(){
        /**
         id
         share_id 股票id
         price 买入价格
         num 买入数量
         day 买入时间
         state 状态 0 ->买入 1->卖出
         outprice 买出价格
         outday 买出时间
         fee 手续费
         */
        do {
            let table = Table("Store")
            if false == isExitTable(tname: "Store"){
                try db?.run(table.create { t in
                    t.column(Expression<Int64>("id"), primaryKey: true)
                    t.column(Expression<Int64?>("share_id"))
                    t.column(Expression<Float64>("price"))
                    t.column(Expression<Int64>("num"))
                    t.column(Expression<String?>("day"))
                })
                UserDefaults.standard.setValue("1", forKey: "db_store")
            }
            var shareVersion:Int = UserDefaults.standard.integer(forKey: "db_store")
//            shareVersion = 1
            if shareVersion == 1 {
         
                try db?.run(table.addColumn(Expression<Int64?>("state")))
                try db?.run(table.addColumn(Expression<Float64?>("outprice")))
                try db?.run(table.addColumn(Expression<String?>("outday")))
                try db?.run(table.addColumn(Expression<Float64?>("fee")))
                UserDefaults.standard.setValue("2", forKey: "db_store")
            }
            
        }catch {
            debugPrint("\(self) \(#function) SQLiteManage install error \(error)")
        }
    }
    
}
